Course: Advanced Excel Training
Topic: Using Goal Seek for Target-Based Calculations
Course Link: Advanced Excel Course
Demo Link: Register for a Course Demo
Website: JustAcademy
Goal Seek is a What-If Analysis tool in Microsoft Excel that helps users determine the input value required to achieve a specific result in a formula. Instead of manually trying different numbers, users specify the desired result and allow Excel to calculate the required input.
Goal Seek is especially useful when a formula's output depends on one unknown input. It works backward from a target value to determine which input value makes the formula return the desired result.
For example, a business may want to determine how many products it must sell to earn a profit of ₹1,00,000. If profit depends on sales quantity, selling price, and costs, Goal Seek can calculate the required sales quantity when the other values are fixed.
Goal Seek is a built-in Excel feature available through the What-If Analysis tools. It changes one input cell until a formula in another cell reaches a specified target value.
Goal Seek requires three essential elements:
| Element | Description | Example |
|---|---|---|
| Set Cell | The cell containing the formula whose result must reach a target. | Profit formula in D2 |
| To Value | The target result Excel should achieve. | 100000 |
| By Changing Cell | The input cell Excel is allowed to change. | Units Sold in B2 |
The Set Cell must contain a formula that depends directly or indirectly on the Changing Cell. The target value is entered as a number, not as a cell reference.
Follow these steps to open Goal Seek:
In most desktop versions of Microsoft Excel for Windows, the menu path is Data > What-If Analysis > Goal Seek. The precise ribbon arrangement may differ across Excel versions and platforms.
The Goal Seek dialog box contains three fields. Each field must be completed correctly for Excel to calculate the required input.
The Set Cell field identifies the formula cell that contains the result you want to achieve. For example, if cell D2 calculates profit, D2 should be selected as the Set Cell.
Important: The Set Cell must contain a formula. A cell containing only a fixed number cannot be used to calculate a target through Goal Seek.
The To Value field contains the desired numerical result. For example, enter 100000 when you want the formula to return a profit of ₹1,00,000.
The By Changing Cell field identifies the input that Excel can adjust. For example, B2 may contain the number of units sold.
The changing cell must be a single input cell. Goal Seek does not directly change multiple independent input cells at the same time.
Suppose a company sells a product for ₹500 per unit and incurs a variable cost of ₹300 per unit. Its fixed costs are ₹40,000. The company wants to determine how many units it must sell to achieve a profit of ₹1,00,000.
| Cell | Description | Value or Formula |
|---|---|---|
| A1 | Selling Price per Unit | 500 |
| A2 | Variable Cost per Unit | 300 |
| A3 | Fixed Costs | 40000 |
| A4 | Units Sold | 500 |
| A5 | Total Revenue | =B1*B4 |
| A6 | Total Variable Cost | =B2*B4 |
| A7 | Total Profit | =B5-B6-B3 |
Enter the descriptions in column A and their corresponding values or formulas in column B. The formulas in the table refer to the cells in column B.
The profit formula is:
Profit = (Selling Price - Variable Cost) * Units Sold - Fixed CostsUsing the initial values, the profit is:
Profit = (500 - 300) * 500 - 40000
Profit = 60000The current profit is ₹60,000, but the business wants to achieve ₹1,00,000.
| Field | Enter |
|---|---|
| Set Cell | $B$7 |
| To Value | 100000 |
| By Changing Cell | $B$4 |
The required number of units is 700.
Profit = (500 - 300) * 700 - 40000
Profit = 100000The company must sell 700 units to achieve a profit of ₹1,00,000 under the stated assumptions.
Suppose a business sells 1,000 units. The variable cost is ₹250 per unit, and fixed costs are ₹50,000. The business wants to earn a profit of ₹2,00,000 and needs to determine the required selling price.
| Cell | Description | Value or Formula |
|---|---|---|
| B1 | Selling Price per Unit | 500 |
| B2 | Variable Cost per Unit | 250 |
| B3 | Units Sold | 1000 |
| B4 | Fixed Costs | 50000 |
| B5 | Total Profit | =(B1-B2)*B3-B4 |
Goal Seek calculates the selling price required to achieve the target profit.
Target Profit = (Selling Price - 250) * 1000 - 50000
200000 = (Selling Price - 250) * 1000 - 50000
Selling Price = 500The required selling price is ₹500 per unit.
Goal Seek can also be used to determine the marks required in an examination to achieve a target overall percentage.
Suppose a student has scored 70 out of 100 in each of three subjects and wants an average score of 80 across four equally weighted subjects.
| Cell | Description | Value or Formula |
|---|---|---|
| B1 | Subject 1 | 70 |
| B2 | Subject 2 | 70 |
| B3 | Subject 3 | 70 |
| B4 | Subject 4 | 70 |
| B5 | Average Marks | =AVERAGE(B1:B4) |
Goal Seek calculates the marks needed in Subject 4 to achieve an average of 80.
Required Average = (70 + 70 + 70 + Required Marks) / 4
80 = (210 + Required Marks) / 4
Required Marks = 110The mathematical result is 110 marks, which exceeds the maximum of 100. Therefore, the target is impossible under these assumptions. Goal Seek can help reveal that a target cannot be achieved within realistic input limits. In this example, even scoring 100 would produce an average of 77.5.
Suppose a person wants to save ₹1,20,000 in 12 months. If savings are equal every month and interest is ignored, the monthly savings target can be calculated using Goal Seek.
| Cell | Description | Value or Formula |
|---|---|---|
| B1 | Monthly Savings | 8000 |
| B2 | Number of Months | 12 |
| B3 | Total Savings | =B1*B2 |
Total Savings = Monthly Savings * Number of Months
120000 = Monthly Savings * 12
Monthly Savings = 10000The required monthly savings are ₹10,000, assuming no interest, fees, or changes in the savings schedule.
A manufacturing company wants to achieve a target contribution after accounting for fixed costs. Goal Seek can calculate the required production or sales quantity when unit revenue, unit variable cost, and fixed costs are known.
Suppose the selling price is ₹800 per unit, variable cost is ₹500 per unit, fixed costs are ₹90,000, and the target profit is ₹1,50,000.
Profit = (Selling Price - Variable Cost) * Quantity - Fixed Costs
150000 = (800 - 500) * Quantity - 90000
240000 = 300 * Quantity
Quantity = 800 unitsThe company must sell 800 units to reach the target profit, assuming the unit selling price and variable costs remain constant and sufficient demand exists.
Goal Seek can be used with a financial formula to determine an input needed to achieve a target payment or balance. For example, users may calculate a loan amount that corresponds to a specified monthly payment when the interest rate and loan term are fixed.
Excel's PMT function calculates the periodic payment for a loan based on a constant interest rate and payment schedule.
=PMT(B2/12,B3*12,B1)In this example:
The formula returns a payment with a sign convention that usually makes the payment negative when the principal is entered as a positive value. To use Goal Seek for a positive target payment, you can set the target cell to the negative of PMT or use =-PMT(B2/12,B3*12,B1).
Set the payment formula cell as the Set Cell, enter the desired payment in To Value, and select the loan principal as the Changing Cell. This determines the loan principal that corresponds to the specified payment, assuming the interest rate and term remain fixed.
Actual loan payments may also depend on fees, insurance, variable interest rates, and lender-specific terms.
Businesses can use Goal Seek to determine the input required to achieve a budget target, reduce expenses to a desired level, or calculate the revenue needed to meet a financial objective.
For each use case, build a formula that accurately represents the business relationship before applying Goal Seek. The output is only as reliable as the assumptions in the formula.
After running Goal Seek, Excel displays a status message indicating whether it found a solution. Users can retain the calculated input or cancel the operation.
Goal Seek is a numerical search tool. It may find an approximate result within Excel's calculation tolerance rather than an exact mathematical equality in every situation.
| Limitation | Explanation |
|---|---|
| One changing cell | Goal Seek adjusts only one input cell per operation. |
| Requires a formula | The Set Cell must contain a formula linked to the changing input. |
| No direct multiple-variable optimization | It cannot independently optimize several changing cells at once. |
| May return unrealistic values | It does not automatically enforce business limits such as nonnegative quantities or maximum prices. |
| May not find a suitable result | Some formulas do not reach the target, or the numerical search may not find an appropriate solution. |
| Changes the workbook input | The selected changing cell is modified when the result is retained. |
| Limited to one target per run | Separate target calculations normally require separate Goal Seek operations. |
| Feature | Goal Seek | Solver | Data Table |
|---|---|---|---|
| Main purpose | Find an input for a target formula result. | Optimize an objective under constraints. | Compare results for multiple input scenarios. |
| Changing inputs | One cell. | Multiple cells can be adjusted. | One or two input variables for a What-If Data Table. |
| Constraints | No direct constraints. | Supports specified constraints. | No optimization constraints. |
| Example | Find sales units required for ₹1,00,000 profit. | Maximize profit subject to resource limits. | Compare profit at different prices and quantities. |
Use Goal Seek when a single input must produce a specific formula result. Use Solver for more complex optimization problems involving multiple changing cells and constraints. Use Data Tables when you want to examine how different input values affect a formula's result.
| Problem | Possible Cause | Solution |
|---|---|---|
| Goal Seek option is unavailable. | The user is working in a version or platform with different feature availability. | Check the supported Excel desktop version and ribbon location. |
| Set Cell contains no formula. | A fixed number was selected instead of a formula cell. | Select a formula cell that depends on the changing input. |
| Result does not reach the target. | The formula may not be able to reach the target. | Check the formula, assumptions, and feasible input range. |
| Incorrect cell changes. | The wrong Changing Cell was selected. | Reopen Goal Seek and select the intended input cell. |
| Result is negative or unrealistic. | No business constraints were applied. | Check whether the result is feasible and use Solver if constraints are required. |
| Formula does not update as expected. | Calculation settings or formula dependencies may be incorrect. | Check formula references and workbook calculation settings. |
| Whole-unit output is needed. | The mathematical result may be fractional. | Use appropriate rounding and recalculate the formula with the rounded input. |
Task: A company sells a product for ₹750 per unit. The variable cost is ₹450 per unit, and monthly fixed costs are ₹60,000. The company wants to earn a monthly profit of ₹1,50,000.
Profit = (Selling Price - Variable Cost) * Units Sold - Fixed Costs
150000 = (750 - 450) * Units Sold - 60000
210000 = 300 * Units Sold
Units Sold = 700The company must sell 700 units to achieve the target profit, assuming the price, variable cost, and fixed costs remain constant.
Answer: Goal Seek is a What-If Analysis tool that determines the input value required for a formula to achieve a specified target result.
Answer: In compatible Excel desktop versions, go to Data > What-If Analysis > Goal Seek.
Answer: Set Cell, To Value, and By Changing Cell.
Answer: No. Goal Seek adjusts one changing cell per operation. Solver can adjust multiple decision variables.
Answer: No. The Set Cell must contain a formula that depends on the selected changing cell.
Answer: It can determine the sales quantity or selling price required to achieve a specified revenue or profit target.
Answer: Goal Seek changes one input to reach a target result, whereas Solver can optimize an objective using multiple changing cells and constraints.
Answer: Goal Seek does not automatically apply business constraints, such as minimum selling prices, maximum production capacity, or nonnegative quantities.
Answer: Excel displays a status message. The user can retain the calculated value or cancel the operation.
Answer: Yes. If a formula calculates the average or total marks, Goal Seek can determine the required score for a target, provided the target is feasible under the formula's assumptions.
Goal Seek is a useful Advanced Excel feature for solving target-based calculations. It helps users determine the input required to achieve a desired result in a formula, reducing manual trial and error.
From calculating sales targets and product prices to planning budgets, monthly savings, and loan payments, Goal Seek can simplify many everyday business and analytical tasks. Accurate formulas, realistic assumptions, and careful validation are essential for obtaining meaningful results.
Explore the Advanced Excel Course at JustAcademy to strengthen your spreadsheet analysis and What-If Analysis skills.
To learn more about training options, register for a course demo or visit the JustAcademy website.
